<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class File_Model extends CI_Model {
	
	public function check_file($id, $username) {
		$query = "SELECT * FROM smb_student_files WHERE id = ? AND username = ? ";
		$result = $this->db->query($query, array($id, $username));
		if ($result->num_rows() == 1) {
			return $result->row();
		} else {
			return NULL;
		}
	}
	
	public function get_files($username) {
		$query = "SELECT sfc.id, ssf.upload_time, ssf.details, sfc.name 
			FROM smb_student_files AS ssf JOIN smb_file_categories 
			AS sfc ON ssf.id = sfc.id WHERE username = ?";
		$result = $this->db->query($query, array($username));
		if ($result->num_rows() > 0) {
			return $result->result();
		} else {
			return NULL;
		}
	}
	
	public function get_category_by_username_and_id($username, $id) {
		$query = "SELECT sfc.name FROM smb_student_files AS ssf JOIN smb_file_categories
			AS sfc ON ssf.id = sfc.id WHERE username = ? and sfc.id = ?";
		$result = $this->db->query($query, array($username, $id));
		if ($result->num_rows() != 1) {
			return NULL;
		} else {
			return $result->row();
		}
	}
	
	public function get_categories() {
		$query = "SELECT * FROM smb_file_categories";
		$result = $this->db->query($query);
		if ($result->num_rows() == 0) {
			return NULL;
		} else {
			return $result->result();
		}
	}
	
	public function insert_student_file($id, $username, $details) {
		$query = "INSERT INTO smb_student_files (id, username, upload_time, details) VALUES (?, ?, CURRENT_TIMESTAMP, ?)";
		
		$this->db->trans_start();
		$this->db->query($query, array($id, $username, $details));
		$this->db->trans_complete();
		
		if ($this->db->trans_status() === FALSE) {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return FALSE;
		} else {
			return TRUE;
		}
	}
	
	public function update_student_file($id, $username, $details) {
		$query = "UPDATE smb_student_files SET upload_time = CURRENT_TIMESTAMP, details = ? WHERE id = ? and username = ?";
		
		$this->db->trans_start();
		$this->db->query($query, array($details, $id, $username));
		$this->db->trans_complete();
		
		if ($this->db->trans_status() === FALSE) {
			$this->session->set_flashdata('error', 'Terjadi kesalahan saat akses database.');
			return FALSE;
		} else {
			return TRUE;
		}
	}
	
	public function delete_student_file($id, $username, $name) {
		$query = "DELETE FROM smb_student_files WHERE id = ? and username = ?";
		
		$this->db->trans_begin();
		$this->db->query($query, array($id, $username));
		
		if ($this->db->trans_status() === FALSE) {
			$this->db->trans_rollback();
			return FALSE;
		} else {
			$filename = 'files/studentdocument/'.do_hash($username.' - '.$name.'.pdf', 'sha1');
			$result = unlink($filename);
			if ($result == TRUE) {
				$this->db->trans_commit();
				return TRUE;
			} else {
				$this->db->trans_rollback();
				return FALSE;	
			}
		}
	}
	
	public function get_category_by_id($id) {
		$query = "SELECT name FROM smb_file_categories WHERE id = ?";
		$result = $this->db->query($query, array($id));
		if ($result->num_rows() == 0) {
			return NULL;
		} else {
			return $result->row();
		}
	}
	
	public function is_unique_files($files) {
		$in = '';
		foreach ($files as $key => $file) {
			if ($file == '') {
				continue;
			} else {
				$in = $in.'?, ';
			}
		}
		if ($in == '') {
			return TRUE;
		} else {
			$in = substr($in, 0, -2);
			$query = "SELECT * FROM smb_file_categories WHERE name IN (".$in.")";
			$result = $this->db->query($query, $files);
			if ($result->num_rows() > 0) {
				return FALSE;
			} else {
				return TRUE;
			}
		}
	}
}